-- *******************************************
-- * Overall Definitions 
-- *
-- CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE C;

CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/opt/local/lib/postgresql84/plpgsql.so' LANGUAGE C;
CREATE TRUSTED LANGUAGE plpgsql HANDLER plpgsql_call_handler;
--
-- *
-- *********************

-- *******************************************
-- *  DB Session Table 
-- *
CREATE TABLE sessions2(
 sesskey VARCHAR( 64 ) NOT NULL DEFAULT '',
 expiry TIMESTAMP NOT NULL ,
 expireref VARCHAR( 250 ) DEFAULT '',
 created TIMESTAMP NOT NULL ,
 modified TIMESTAMP NOT NULL ,
 sessdata TEXT DEFAULT '',
 PRIMARY KEY ( sesskey )
 );
create INDEX sess2_expiry on sessions2( expiry );
create INDEX sess2_expireref on sessions2 ( expireref );
--
-- *
-- **********************

-- *******************************************
-- * Work Flow States
-- *
CREATE TABLE workflowstates(
    id SERIAL,
    name VARCHAR(255),
    protected BOOL DEFAULT FALSE,
    sort_order SERIAL UNIQUE NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO workflowstates(id, name, protected) VALUES(1,'PUBLISHED',TRUE);
ALTER SEQUENCE workflowstates_id_seq RESTART WITH 100;
--
-- *
-- *********************


-- *******************************************
-- * Object Type Definition Table
-- *
-- * Access to this table shall be limited to
-- * selecting data only. Data updates and insertion
-- * shall be accessible to the DBA only
-- *
CREATE TABLE datatype(
    id SERIAL,
    name VARCHAR(255),
    codeset VARCHAR(255),
    folder bool DEFAULT false,
    adminadd bool DEFAULT true,
    PRIMARY KEY (id),
    CONSTRAINT datatype_codeset_check UNIQUE (codeset)
);

INSERT INTO datatype(id, name, codeset, adminadd) VALUES(1,'Start Page','root',false);
ALTER SEQUENCE datatype_id_seq RESTART WITH 100;
--
-- *
-- **********************



-- *******************************************
-- * User Groups Table
-- *
CREATE TABLE user_group(
    id SERIAL,
    name VARCHAR(255) NOT NULL,
    created TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id)
);

INSERT INTO user_group(id,name) VALUES(1,'Super Users');
INSERT INTO user_group(id,name) VALUES(1000,'www');
ALTER SEQUENCE user_group_id_seq RESTART WITH 1002;
--
-- *
-- **********************


-- *******************************************
-- * User Access Table
-- *
CREATE TABLE user_access(
    id SERIAL,
    login   VARCHAR(255) NOT NULL,
    passwd  VARCHAR(255) NOT NULL,
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (login)
);
INSERT INTO user_access(id,login,passwd) VALUES(1,'admin',md5('admin'));
INSERT INTO user_access(id,login,passwd) VALUES(1000,'www',md5('www'));
ALTER SEQUENCE user_access_id_seq RESTART WITH 1002;
--
-- *
-- *********************


-- *******************************************
-- * User Data Table
-- *
CREATE TABLE user_data(
    uid         INTEGER,
    fullname    VARCHAR(255),
    email       VARCHAR(255),
    note        VARCHAR(255),
    title       VARCHAR(16),
    gender      VARCHAR(1) CONSTRAINT valid_gender CHECK (gender = 'm' OR gender = 'f'),
    dob         TIMESTAMP,
    FOREIGN KEY(uid) REFERENCES user_access ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE (uid)
);
INSERT INTO user_data(uid,fullname) VALUES(1,'Admin Smith');
--
-- *
-- *********************


-- *******************************************
-- * User Blocked Access Table
-- *
CREATE TABLE user_blocked(
    id      SERIAL,
    ip      INTEGER DEFAULT NULL,
    uid     INTEGER DEFAULT NULL,
    expires TIMESTAMP NOT NULL DEFAULT NOW() + interval '1 hour',
    FOREIGN KEY(uid) REFERENCES user_access ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(id),
    UNIQUE (uid)
);
--
-- *
-- *********************


-- *******************************************
-- * User To Group Mapping
-- *
CREATE TABLE user_in_group(
    uid     INTEGER NOT NULL,
    gid     INTEGER DEFAULT 1000,
    FOREIGN KEY(uid) REFERENCES user_access ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(gid) REFERENCES user_group ON DELETE SET DEFAULT ON UPDATE CASCADE,
    CONSTRAINT user_in_group_check UNIQUE (uid, gid)
);
INSERT INTO user_in_group(uid,gid) VALUES(1,1);
--
-- *
-- *********************



-- *******************************************
-- * Control Panel Data Lists
-- *
-- * Access to this table shall be limited to
-- * selecting data only. Data updates and insertion
-- * shall be accessible to the DBA only
-- *
CREATE TABLE user_cp_access(
    id SERIAL,
    name    VARCHAR(255) NOT NULL,
    codeset VARCHAR(255) NOT NULL UNIQUE,
    istab   BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (id)
);
CREATE INDEX user_cp_access_codeset ON user_cp_access(codeset);
ALTER SEQUENCE user_cp_access_id_seq RESTART WITH 100;
--
-- *
-- *********************


-- *******************************************
-- * Control Panel Data Lists
-- *
CREATE TABLE user_in_cp(
    uid     INTEGER NOT NULL,
    cpid    INTEGER NOT NULL,
    FOREIGN KEY(uid) REFERENCES user_access ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(cpid) REFERENCES user_cp_access ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT user_in_cp_check UNIQUE (uid, cpid)
);
--
-- *
-- *********************


-- *******************************************
-- * Control Panel Data Lists
-- *
CREATE TABLE user_group_in_cp(
    gid     INTEGER NOT NULL,
    cpid    INTEGER NOT NULL,
    FOREIGN KEY(gid) REFERENCES user_group ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(cpid) REFERENCES user_cp_access ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT group_in_cp_check UNIQUE (gid, cpid)
);
--
-- *
-- *********************


-- *******************************************
-- * Log data tables
-- *
CREATE TABLE log_error(
    id      SERIAL,
    intime  TIMESTAMP DEFAULT NOW(),
    code    INT2,
    message text,
    page    text,
    ip      VARCHAR(32) NOT NULL,
    uid     INTEGER,
    FOREIGN KEY(uid) REFERENCES user_access ON DELETE SET NULL ON UPDATE SET NULL,
    PRIMARY KEY (id)
);


CREATE TABLE log_action(
    id      SERIAL,
    intime  TIMESTAMP DEFAULT NOW(),
    code    INT2,
    message text,
    page    text,
    ip      VARCHAR(32) NOT NULL,
    uid     INTEGER,
    FOREIGN KEY(uid) REFERENCES user_access ON DELETE SET NULL ON UPDATE SET NULL,
    PRIMARY KEY (id)
);
--
-- *
-- *********************



-- **********************************************************
-- * Solution Structure, this is where all the magic happens
-- *
CREATE TABLE structure (
    id          SERIAL,
    url         VARCHAR(64) NOT NULL,
    parent_id   INTEGER,
    level       INTEGER NOT NULL,
    sort_order  INTEGER NOT NULL DEFAULT 1,
    lft         INTEGER NOT NULL,
    rgt         INTEGER NOT NULL,
    path_cache  VARCHAR(255) DEFAULT NULL,
    node_type   INTEGER DEFAULT NULL,
    authorize   BOOLEAN DEFAULT FALSE,
    PRIMARY KEY(id),
    FOREIGN KEY(node_type) REFERENCES datatype ON UPDATE CASCADE,
    FOREIGN KEY(parent_id) REFERENCES structure ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT structure_valid_lft CHECK (lft > 0),
    CONSTRAINT structure_valid_rgt CHECK (rgt > 1),
    CONSTRAINT structure_rgt_gt_lft CHECK (rgt > lft),
    UNIQUE (url, parent_id)
);
CREATE INDEX structure_ns_lft ON structure (lft);
CREATE INDEX structure_ns_rgt ON structure (rgt);
-- 
-- *
-- *********************


-- **********************************************************
-- * Node Properties -- the most important thing in site design
-- *
CREATE TABLE properties(
    id      SERIAL,
    name    VARCHAR(255) NOT NULL,
    value   VARCHAR(255) NOT NULL,
    inherit BOOLEAN DEFAULT TRUE,
    PRIMARY KEY(id)
);


CREATE TABLE property_node_mapping(
    nodeid  INTEGER NOT NULL,
    propid  INTEGER NOT NULL,
    FOREIGN KEY(nodeid) REFERENCES structure ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(propid) REFERENCES properties ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE(nodeid,propid)
);


-- 
-- *
-- *********************


-- **********************************************************
-- * Datablocks -- an important thing in site design
-- *
CREATE TABLE datablocks(
    id      SERIAL,
    title   VARCHAR(255),
    content TEXT,
    tag     VARCHAR(255),
    global  BOOLEAN DEFAULT FALSE,
    inherit BOOLEAN DEFAULT FALSE,
    PRIMARY KEY(id)
);
CREATE INDEX datablock_tag ON datablocks(tag);


CREATE TABLE datablock_node_mapping(
    blockid INTEGER NOT NULL,
    nodeid  INTEGER NOT NULL,
    inherit bool DEFAULT false,
    FOREIGN KEY(nodeid) REFERENCES structure ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(blockid) REFERENCES datablocks ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE(nodeid,blockid)
);

-- 
-- *
-- *********************



-- **********************************************************
-- * Concent Versions, Originally only one
-- *
CREATE TABLE version(
    id SERIAL,
    name VARCHAR(255) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

INSERT INTO version(id, name) VALUES(1,'Original');
ALTER SEQUENCE version_id_seq RESTART WITH 2;
-- 
-- *
-- *********************


-- **********************************************************
-- * Content Map between node and folder objects
-- *
CREATE TABLE objectmap(
    nodeid      INTEGER NOT NULL,
    version     INTEGER NOT NULL DEFAULT 1,
    objectid    INTEGER NOT NULL DEFAULT 1,
    title       VARCHAR(255),
    wf_state    INTEGER DEFAULT NULL,
    owner_uid   INTEGER DEFAULT 1,
    owner_gid   INTEGER DEFAULT 1,
    created     TIMESTAMP DEFAULT NOW(),
    published   TIMESTAMP DEFAULT NULL,
    modified    TIMESTAMP DEFAULT NOW(),
    pages	    INTEGER NOT NULL  DEFAULT 0,
    published_pages INTEGER NOT NULL  DEFAULT 0,
    FOREIGN KEY(version)    REFERENCES version          ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(nodeid)     REFERENCES structure        ON DELETE CASCADE,
    FOREIGN KEY(wf_state)   REFERENCES workflowstates   ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY(owner_uid)  REFERENCES user_access      ON DELETE SET DEFAULT ON UPDATE CASCADE,
    FOREIGN KEY(owner_gid)  REFERENCES user_group       ON DELETE SET DEFAULT ON UPDATE CASCADE,
    UNIQUE (nodeid, version)
);
-- 
-- *
-- *********************

-- **********************************************************
-- * Content Map between node and page objects
-- *
CREATE TABLE pagemap(
    id          SERIAL,
    nodeid      INTEGER NOT NULL,
    version     INTEGER NOT NULL DEFAULT 1,
    pageurl     VARCHAR(255) NOT NULL,
    pageid      INTEGER NOT NULL,
    pagetype    INTEGER NOT NULL,
    title       VARCHAR(255) DEFAULT 'n/a',
    created     TIMESTAMP DEFAULT NOW(),
    published   TIMESTAMP DEFAULT NULL,
    modified    TIMESTAMP DEFAULT NOW(),
    expires     TIMESTAMP DEFAULT NULL,
    wf_state    INTEGER DEFAULT NULL,
    owner_uid   INTEGER DEFAULT NULL,
    owner_gid   INTEGER DEFAULT NULL,	
    PRIMARY KEY(id),
    FOREIGN KEY(nodeid)     REFERENCES structure        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(version)    REFERENCES version          ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(pagetype)   REFERENCES datatype         ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY(wf_state)   REFERENCES workflowstates   ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY(owner_uid)  REFERENCES user_access      ON DELETE SET DEFAULT ON UPDATE CASCADE,
    FOREIGN KEY(owner_gid)  REFERENCES user_group       ON DELETE SET DEFAULT ON UPDATE CASCADE,
    UNIQUE(nodeid, pageurl)
);
-- 
-- *
-- *********************


CREATE TABLE datablock_page_mapping(
    pageid  INTEGER NOT NULL,
    blockid  INTEGER NOT NULL,
    FOREIGN KEY(pageid) REFERENCES pagemap ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(blockid) REFERENCES datablocks ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE(pageid,blockid)
);


CREATE TABLE property_page_mapping(
    pageid  INTEGER NOT NULL,
    propid  INTEGER NOT NULL,
    FOREIGN KEY(pageid) REFERENCES pagemap ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(propid) REFERENCES properties ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE(pageid,propid)
);


-- *******************************************
-- * Search Index -- this is some sort of voodoo
-- * that may need optimization in the future,
-- * currently this is just a breed for easy use
-- *
CREATE TABLE searchindex(
    id          SERIAL,
    pageid      INTEGER,
    longindex   TEXT,
    created     TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id),
    FOREIGN KEY (pageid) REFERENCES pagemap ON DELETE CASCADE ON UPDATE CASCADE
);
--
-- *
-- *********************


-- **********************************************************
-- * Some Triggers
-- *
--
-- This trigger is responsible for keeping pagecounts intact
--
CREATE OR REPLACE FUNCTION recount_insert() RETURNS TRIGGER AS '
DECLARE
    myLeft          integer;
	myRight         integer;
BEGIN
	
    SELECT lft, rgt FROM structure WHERE id = NEW.nodeid INTO myLeft, myRight;
    UPDATE objectmap SET pages = pages + 1 FROM structure WHERE objectmap.nodeid = structure.id AND structure.lft <= myLeft AND structure.rgt >= myRight AND objectmap.version = NEW.version;

	RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER maintain_filecount_insert
AFTER INSERT ON pagemap
FOR EACH ROW EXECUTE PROCEDURE recount_insert();


-------
--
-- This trigger is responsible for keeping pagecounts intact
--
CREATE OR REPLACE FUNCTION recount_update() RETURNS TRIGGER AS '
DECLARE
    myLeft          integer;
	myRight         integer;
BEGIN
	
    SELECT lft, rgt FROM structure WHERE id = NEW.nodeid INTO myLeft, myRight;
    
	IF OLD.published IS NULL AND NEW.published IS NOT NULL THEN
	
	     UPDATE objectmap SET published_pages = published_pages + 1 FROM structure WHERE objectmap.nodeid = structure.id AND structure.lft <= myLeft AND structure.rgt >= myRight AND objectmap.version = OLD.version;
    END IF;
    
    IF OLD.published IS NOT NULL AND NEW.published IS NULL THEN

	     UPDATE objectmap SET published_pages = published_pages - 1 FROM structure WHERE objectmap.nodeid = structure.id AND structure.lft <= myLeft AND structure.rgt >= myRight AND objectmap.version = OLD.version;

    END IF;
    
	RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER maintain_filecount_update
AFTER UPDATE ON pagemap
FOR EACH ROW EXECUTE PROCEDURE recount_update();


-------
--
-- This trigger is responsible for keeping pagecounts intact
--
CREATE OR REPLACE FUNCTION recount_delete() RETURNS TRIGGER AS '
DECLARE
    myLeft          integer;
	myRight         integer;
BEGIN
	
    SELECT lft, rgt FROM structure WHERE id = OLD.nodeid INTO myLeft, myRight;
    		
	IF OLD.published IS NOT NULL THEN
		UPDATE objectmap SET pages = pages - 1, published_pages = published_pages - 1 FROM structure WHERE objectmap.nodeid = structure.id AND structure.lft <= myLeft AND structure.rgt >= myRight AND objectmap.version = OLD.version;
	ELSE
		UPDATE objectmap SET pages = pages - 1 FROM structure WHERE objectmap.nodeid = structure.id AND structure.lft <= myLeft AND structure.rgt >= myRight AND objectmap.version = OLD.version;
	END IF;
	
	RETURN OLD;
END;
' language 'plpgsql';

CREATE TRIGGER maintain_filecount_delete
BEFORE DELETE ON pagemap
FOR EACH ROW EXECUTE PROCEDURE recount_delete();


-------
--
-- The trigger below makes sure the pages and folder maintain
-- a last modified timestamp along with a publication time
--
CREATE OR REPLACE FUNCTION setmodtime() RETURNS TRIGGER AS '
	BEGIN
	   NEW.modified = now();
	   
	  
       IF NEW.wf_state = 1 THEN
           IF NEW.published IS NULL THEN
                NEW.published = now();
           END IF;
       ELSE
           NEW.published = NULL;
       END IF;
	 
        
	   RETURN NEW;
	END
' LANGUAGE 'plpgsql';


--
-- The trigger below makes sure the data maintains
-- a last modified timestamp
--
CREATE OR REPLACE FUNCTION setmodtime_simple() RETURNS TRIGGER AS '
	BEGIN
	   NEW.modified = now();
        
	   RETURN NEW;
	END
' LANGUAGE 'plpgsql';


CREATE TRIGGER page_modified BEFORE INSERT OR UPDATE ON pagemap 
FOR EACH ROW EXECUTE PROCEDURE setmodtime();

CREATE TRIGGER folder_modified BEFORE INSERT OR UPDATE ON objectmap 
FOR EACH ROW EXECUTE PROCEDURE setmodtime();

CREATE TRIGGER user_pass_modified BEFORE INSERT OR UPDATE ON user_access 
FOR EACH ROW EXECUTE PROCEDURE setmodtime_simple();


CREATE OR REPLACE FUNCTION setmodtimeSimple() RETURNS TRIGGER AS '
	BEGIN
	   NEW.modified = now();
	   RETURN NEW;
	END
' LANGUAGE 'plpgsql';


-------
--
-- This trigger makes sure that any new codeset registered with 
-- the system is automatically granted access with the admin
-- group, also basic codesets are registered with all admin groups
--
CREATE OR REPLACE FUNCTION authorize_admin() RETURNS TRIGGER AS '
DECLARE
    mygroup RECORD;
BEGIN
    --all groups are notified
    IF NEW.id < 100 THEN
        FOR mygroup IN SELECT id FROM user_group WHERE id < 1000 LOOP
            INSERT INTO user_group_in_cp(gid, cpid) VALUES(mygroup.id,NEW.id);
        END LOOP;
    
    --only the base admin group is notified
    ELSE
        INSERT INTO user_group_in_cp(gid, cpid) VALUES(1,NEW.id);
    END IF;
	
	RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER authorize_admin_group
AFTER INSERT ON user_cp_access
FOR EACH ROW EXECUTE PROCEDURE authorize_admin();

--
-- This trigger makes sure that any new group registered with 
-- the system is automatically granted access with the basic 
-- codesets, such as logging in, 404 pages and the overview page
--
CREATE OR REPLACE FUNCTION authorize_group() RETURNS TRIGGER AS '
DECLARE
    mypermission RECORD;
BEGIN
    --the new group is an admin group
    IF NEW.id < 1000 THEN
        FOR mypermission IN SELECT id FROM user_cp_access WHERE id < 100 LOOP
            INSERT INTO user_group_in_cp(cpid,gid) VALUES(mypermission.id,NEW.id);
        END LOOP;
    END IF;
	
	RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER authorize_new_group
AFTER INSERT ON user_group
FOR EACH ROW EXECUTE PROCEDURE authorize_group();

------
-- very important clean-up trigger
-- that makes sure old session data and old log data is dumped
--
CREATE OR REPLACE FUNCTION cleanup_sessions() RETURNS TRIGGER AS '
BEGIN
    DELETE FROM sessions2 WHERE expiry < NOW();
    RETURN NULL;
END;
' language 'plpgsql';

CREATE TRIGGER dump_old_sessions
AFTER UPDATE OR INSERT ON sessions2
FOR EACH STATEMENT EXECUTE PROCEDURE cleanup_sessions();

--
-- Clean Logs
--
CREATE OR REPLACE FUNCTION cleanup_errorlog() RETURNS TRIGGER AS '
BEGIN
    DELETE FROM log_error WHERE intime < NOW() - INTERVAL ''2 weeks'';
    RETURN NULL;
END;
' language 'plpgsql';

CREATE TRIGGER dump_old_errors
AFTER INSERT ON log_error
FOR EACH STATEMENT EXECUTE PROCEDURE cleanup_errorlog();


CREATE OR REPLACE FUNCTION cleanup_actionlog() RETURNS TRIGGER AS '
BEGIN
    DELETE FROM log_action WHERE intime < NOW() - INTERVAL ''2 weeks'';
    RETURN NULL;
END;
' language 'plpgsql';

CREATE TRIGGER dump_old_errors
AFTER INSERT ON log_action
FOR EACH STATEMENT EXECUTE PROCEDURE cleanup_actionlog();

-- 
-- *
-- *******************


-- **********************************************************
-- * Some Functions
-- *
--
-- User Functions to work with the trees based on Joe Celkos
-- Nested Set theory and a lot of Voodoo by A. Kulikov
--
-- check dull.ru or essentialmind.com in order to see what else
-- the author is up to
--
-- copyright 2005, A. Kulikov <a.kulikov@gmail.com>
--

--
-- add_node()
--  @param node url
--  @param parent id OR null
--  @param title of node OR null
--
-- this function adds a new node to the tree
--
CREATE or REPLACE FUNCTION add_node (varchar(64),integer,varchar(255),integer) RETURNS INTEGER
AS '

DECLARE

pleft       INTEGER;
pright      INTEGER;
plevel      INTEGER;

newleft     INTEGER;
newright    INTEGER;
newlevel    INTEGER;
newsort     INTEGER;

newnode     INTEGER;
newobject   INTEGER;

nodetitle   VARCHAR(255);
nodetype    INTEGER;

BEGIN

-- Base case ie the root node is inserted
IF $2 IS NULL THEN
    -- Check if a root entry is already present
    SELECT id FROM structure WHERE lft = 1 INTO pleft;

    IF pleft IS NOT NULL THEN
        RAISE EXCEPTION ''A root node already exists with an id of %'',pleft;
    END IF;

    -- Insert Node Reference
    INSERT INTO structure(parent_id,url,level,lft,rgt,path_cache,node_type)
    VALUES(null,'''',0,1,2,'''',1);
    
    -- Get id of new node
    SELECT last_value FROM structure_id_seq INTO newnode;
    
    -- Insert Object Reference
    INSERT INTO node_root(content) VALUES('''');

    -- Get id of new object
    SELECT last_value FROM node_root_id_seq INTO newobject;

    -- Insert Node Name
    INSERT INTO objectmap(nodeid,title,objectid) VALUES(newnode,''Home'',newobject);

    RETURN newnode;
END IF;

-- Check if a name has been specified
IF $3 IS NULL THEN
    nodetitle := $1;
ELSE
    nodetitle := $3;
END IF;

-- Check if a node type has been specified
IF $4 IS NULL THEN
    nodetype  := 2;
ELSE
    nodetype  := $4;
END IF;

-- Get all data relevant to parent
SELECT lft, rgt, level FROM structure WHERE id = $2 INTO pleft, pright, plevel;

-- We have a problem, as parent dont exist
IF pleft IS NULL THEN
  RAISE EXCEPTION ''No parent entry found with an id of %'',$2;
END IF;

-- Get new sort order
SELECT max(sort_order) + 1 FROM structure WHERE parent_id = $2 INTO newsort;

-- Check if newsort was set
IF newsort IS NULL THEN
    newsort := 1;
END IF;

-- Lets do the maths
newleft  := pright;
newright := pright + 1;
newlevel := plevel + 1;

-- Create a gap for the new node
UPDATE structure SET rgt = rgt + 2 WHERE rgt >= pright;
UPDATE structure SET lft = lft + 2 WHERE lft > newleft;

-- Insert New Node
INSERT INTO structure(parent_id,url,level,sort_order,lft,rgt,node_type) 
VALUES($2,$1,newlevel,newsort,newleft,newright,nodetype);

-- Get id of new node
SELECT last_value FROM structure_id_seq INTO newnode;

-- Recalculate Paths
PERFORM get_path(newnode);

-- Insert Node Name and Reference
INSERT INTO objectmap(nodeid,title) VALUES(newnode,nodetitle);

RETURN newnode;

END;
'
LANGUAGE 'plpgsql';

--
-- add_node()
-- 
-- this is a wrapper function for the add_node() defined
-- above. The purpose of this function is to overload
-- the default function with default parameters in order
-- to create the root node
--
CREATE or REPLACE FUNCTION add_node () RETURNS INTEGER
AS '

BEGIN
RETURN add_node(null,null,null,null);
END;
'
LANGUAGE 'plpgsql';

--
-- add_node()
-- 
-- this is a wrapper function for the add_node() defined
-- above. The purpose of this function is to overload
-- the default function with default parameters in order
-- to create a node with no specified title
--
CREATE or REPLACE FUNCTION add_node (varchar(64),integer) RETURNS INTEGER
AS '
BEGIN
RETURN add_node($1,$2,null,null);
END;
'
LANGUAGE 'plpgsql';

--
-- add_node()
-- 
-- this is a wrapper function for the add_node() defined
-- above. The purpose of this function is to overload
-- the default function with default parameters in order
-- to create a node with no specified title
--
CREATE or REPLACE FUNCTION add_node (varchar(64),integer,varchar(255)) RETURNS INTEGER
AS '
BEGIN
RETURN add_node($1,$2,$3,null);
END;
'
LANGUAGE 'plpgsql';


--
-- move_tree_after()
--  @param id of node to move (source)
--  @param id of node to move to (destination)
--  @param id of node after which to place stuff (after)
--
CREATE or REPLACE FUNCTION move_tree_after (integer, integer, integer) RETURNS bool
AS '

DECLARE

cleft INTEGER;     cright INTEGER;  clevel INTEGER;
pleft INTEGER;     pright INTEGER;  plevel INTEGER;

aleft INTEGER;     aright INTEGER;	aparent INTEGER;    asort INTEGER;

leftbound INTEGER; rightbound INTEGER;
treeshift INTEGER; cwidth INTEGER;
leftrangemax INTEGER; rightrangemax INTEGER;
leftrangemin INTEGER; rightrangemin INTEGER;

BEGIN

-- Self-move makes no sense
IF $1 = $2 THEN
  RAISE EXCEPTION ''Cannot move: entries are identical'';
END IF;

-- Gather the data
SELECT lft, rgt, level FROM structure WHERE id = $1 INTO cleft, cright, clevel;
SELECT lft, rgt, level FROM structure WHERE id = $2 INTO pleft, pright, plevel;


-- in case third parameter is not set use default values
IF $3 IS NULL THEN
  aright    := pleft;
  asort     := 1;
ELSE
  SELECT lft, rgt, parent_id, sort_order FROM structure WHERE id = $3 INTO aleft, aright, aparent, asort;

  -- does the special node exist
  IF aleft IS NULL THEN
    RAISE EXCEPTION ''No entry found with an id of %'',$3;
  END IF;

  -- make sure that the special node has the same parent as the one where
  -- we are going to move the child node
  IF aparent != $2 THEN
    RAISE EXCEPTION ''Can not move tree after the specified node, as it does not lie with the same parent as the destination point'';
  END IF;

  -- Set sort order for the new insertion
  asort := asort + 1;  
END IF;


-- Make sure the child exists
IF cleft IS NULL THEN
  RAISE EXCEPTION ''No entry found with an id of %'',$1;
END IF;

-- Make sure the parent exists
IF pleft IS NULL THEN
  RAISE EXCEPTION ''No entry found with an id of %'',$2;
END IF;

-- Parent cannot be underneath the child
IF pleft BETWEEN cleft AND cright THEN
  RAISE EXCEPTION ''Cannot move: first entry contains second'';
END IF;

-- Child may already be in the proper place
IF cleft = pleft+1 THEN
  RAISE EXCEPTION ''No changes need to be made'';
END IF;


-- Do the maths
IF cleft > pleft THEN 
  treeshift     := aright - cleft + 1;
  leftbound     := aright+1;
  rightbound    := cleft-1;
  cwidth        := cright-cleft+1;

  leftrangemax  := cright;
  leftrangemin  := pleft;
  rightrangemax := cright;
  rightrangemin := pleft;
ELSE
  treeshift     := aright - cright;
  leftbound     := cright + 1;
  rightbound    := aright;
  cwidth        := cleft-cright-1;

  leftrangemax  := pleft+1;
  leftrangemin  := cleft;
  rightrangemax := pright;
  rightrangemin := cleft;
END IF;


-- Make place for the new sort order
UPDATE structure 
    SET sort_order = sort_order + 1
WHERE parent_id = $2;


-- Clear the path cache and update levels
UPDATE structure 
    SET 
      path_cache = NULL, 
      level = level + ((plevel + 1) - clevel)
WHERE lft >= cleft AND rgt <= cright;


-- Set new parent and new sort order
UPDATE structure 
    SET parent_id  = $2, sort_order = asort
WHERE id = $1;


-- Move the tree
UPDATE structure
  SET lft = CASE
            WHEN lft BETWEEN leftbound AND rightbound THEN lft + cwidth
            WHEN lft BETWEEN cleft AND cright THEN lft + treeshift
            ELSE lft END,
      rgt = CASE
            WHEN rgt BETWEEN leftbound AND rightbound THEN rgt + cwidth
            WHEN rgt BETWEEN cleft AND cright THEN rgt + treeshift
            ELSE rgt END
WHERE (lft <= leftrangemax AND lft >= leftrangemin) OR (rgt >= rightrangemin AND rgt <= rightrangemax);

-- Recalculate Paths
PERFORM get_path(id) FROM structure;

RETURN TRUE;

END;
'
LANGUAGE 'plpgsql';

--
-- move_tree()
--  @param id of node to move (source)
--  @param id of node to move to (destination)
--
-- this function is a wrapper for the move_tree_after() function
-- defined above. Whenever an after parameter is not defined
-- it is defauled to null and while the tree is moved it is inserted
-- as the first entry on the level
--
CREATE or REPLACE FUNCTION move_tree (integer, integer) RETURNS bool
AS '

BEGIN
RETURN move_tree_after($1,$2,null);
END;
'
LANGUAGE 'plpgsql';


--
-- drop_node()
--  @param id of the node to delete
--
CREATE or REPLACE FUNCTION drop_node (integer) RETURNS BOOL
AS '

DECLARE

mleft       INTEGER; 
mright      INTEGER;
msort       INTEGER;
mparent     INTEGER;
difference  INTEGER;

BEGIN

-- Check if the desired node exists
SELECT lft, rgt, parent_id, sort_order FROM structure WHERE id = $1 INTO mleft, mright, mparent, msort;

IF mleft IS NULL THEN
    RAISE EXCEPTION ''No entry found with an id of %'',$1;
END IF;

-- Drop the node and its subtree
DELETE FROM structure WHERE lft >= mleft AND rgt <= mright;

-- Close the gap
difference := mright - mleft + 1;

UPDATE structure SET lft = lft - difference WHERE lft > mright;
UPDATE structure SET rgt = rgt - difference WHERE rgt > mright;

-- Close the Gap in sort order
UPDATE structure SET sort_order = sort_order - 1 WHERE sort_order > msort AND parent_id = mparent;

RETURN TRUE;

END;
'
LANGUAGE 'plpgsql';


--
-- get_path()
--  @param id of node to which to calculate the paths to
--
CREATE or REPLACE FUNCTION get_path (integer) RETURNS text
AS '

DECLARE

pathtonode VARCHAR(255); 
nodeid INTEGER;
nleft INTEGER;
nright INTEGER;
tempurl RECORD;

BEGIN

--Get some data
SELECT id, path_cache, lft, rgt FROM structure WHERE id = $1 INTO nodeid, pathtonode, nleft, nright;

--Is there such a node at all?
IF nodeid IS NULL THEN
    RAISE EXCEPTION ''No entry found with an id of %'',$1;
END IF;

--Is there a path in the case?
IF pathtonode IS NULL THEN
    --Calculate the path again
    pathtonode := '''';

    FOR tempurl IN SELECT url FROM structure WHERE lft <= nleft AND rgt >= nright AND lft > 1 ORDER BY lft ASC LOOP
        pathtonode := pathtonode||tempurl.url||''/'';
    END LOOP;
    
    IF pathtonode = ''/'' THEN
        pathtonode = '''';
    END IF;
    
    --Store it in cache
    UPDATE structure SET path_cache = pathtonode WHERE id = $1;
END IF;

RETURN pathtonode;

END;
'
LANGUAGE 'plpgsql';
-- 
-- *
-- *********************


-- **********************************************************
-- * Root Node(s)
-- *
CREATE TABLE node_root(
    id      SERIAL,
    content TEXT,
    PRIMARY KEY(id)
);
-- 
-- *
-- *********************


-- **********************************************************
-- * Simple HTML pages
-- *
CREATE TABLE node_page(
    id SERIAL,
    content TEXT,
    subtitle VARCHAR(255),
    PRIMARY KEY(id)
);
-- 
-- *
-- *********************


-- **********************************************************
-- * -- HTML Folder --
-- *
CREATE TABLE node_folder(
    id SERIAL,
    content TEXT,
    PRIMARY KEY(id)
);
-- 
-- *
-- *********************


-- **********************************************************
-- * -- HTML Page Advanced --
-- *
CREATE TABLE node_page_full(
    id SERIAL,
    teaser TEXT,
    content TEXT,
    image BOOL DEFAULT FALSE,
    PRIMARY KEY(id)
);
-- 
-- *
-- *********************


-- **********************************************************
-- * -- HTML Folder Advanced --
-- *
CREATE TABLE node_folder_full(
    id SERIAL,
    teaser TEXT,
    content TEXT,
    image BOOL DEFAULT FALSE,
    PRIMARY KEY(id)
);
-- 
-- *
-- *********************




-- **********************************************************
-- * Trigger and UDF dependant data manipulation
-- *

-- CP Access
INSERT INTO user_cp_access(id,name,codeset,istab)       VALUES(1,'Overview','controlPanel',true);
INSERT INTO user_cp_access(id,name,codeset)             VALUES(2,'Ajax interface','ajax');
INSERT INTO user_cp_access(id,name,codeset)             VALUES(3,'Logout Processor','logout');
INSERT INTO user_cp_access(id,name,codeset)             VALUES(4,'404 Page','404');
INSERT INTO user_cp_access(name,codeset,istab)          VALUES('Content Tree','sitestructure',true);
INSERT INTO user_cp_access(name,codeset,istab)          VALUES('People','users',true);
INSERT INTO user_cp_access(name,codeset,istab)          VALUES('Node Manager','managenodes',true);

-- Datatype Access
INSERT INTO datatype(id, name, codeset)                 VALUES(2,'Page','page');
INSERT INTO datatype(id, name, codeset, folder)			VALUES(3,'Page Folder','folder',true);
INSERT INTO datatype(id, name, codeset)                 VALUES(4,'Page & Teaser','pageadv');
INSERT INTO datatype(id, name, codeset, folder)			VALUES(5,'Page Folder & Teaser','folderadv',true);


-- Creating Root Page
SELECT add_node();



-- ####### ALTER_LOG -- MORE TABLES ########
CREATE TABLE page_ratings(
    pageid      INTEGER NOT NULL,
    pagetype    INTEGER NOT NULL,
    rating      FLOAT NOT NULL DEFAULT 0,
    votes       INTEGER NOT NULL DEFAULT 0,
    
    PRIMARY KEY(pageid, pagetype),
    FOREIGN KEY(pagetype)   REFERENCES datatype         ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE page_ratings_history_anon(
    pageid      INTEGER NOT NULL,
    pagetype    INTEGER NOT NULL,
    ip          INTEGER NOT NULL,
    
    PRIMARY KEY(pageid, pagetype, ip),
    FOREIGN KEY(pagetype)   REFERENCES datatype         ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE page_ratings_history_user(
    pageid      INTEGER NOT NULL,
    pagetype    INTEGER NOT NULL,
    uid         INTEGER NOT NULL,
    
    PRIMARY KEY(pageid, pagetype, uid),
    FOREIGN KEY(pagetype)   REFERENCES datatype         ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY(uid)        REFERENCES user_access      ON DELETE SET NULL ON UPDATE CASCADE
);


CREATE TABLE page_comments(
    comment_id  SERIAL,
    pageid      INTEGER NOT NULL,
    pagetype    INTEGER NOT NULL,
    
    sender_name VARCHAR(100) NOT NULL,
    sender_mail VARCHAR(100) DEFAULT NULL,
    sender_url  VARCHAR(255) DEFAULT NULL,
    
    comment     TEXT NOT NULL,
    
    deleted     BOOL DEFAULT FALSE,
    published   BOOL DEFAULT TRUE,
    
    sortorder   INTEGER NOT NULL DEFAULT 1,
    level       INT2 NOT NULL DEFAULT 1,
    parent      INTEGER DEFAULT NULL,
    
    created     TIMESTAMP DEFAULT NOW(),
    owner_uid   INTEGER DEFAULT NULL,
    owner_gid   INTEGER DEFAULT NULL,
    
    PRIMARY KEY(comment_id),
    FOREIGN KEY(pagetype)   REFERENCES datatype         ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY(parent)     REFERENCES page_comments    ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(owner_uid)  REFERENCES user_access      ON DELETE SET DEFAULT ON UPDATE CASCADE,
    FOREIGN KEY(owner_gid)  REFERENCES user_group       ON DELETE SET DEFAULT ON UPDATE CASCADE
);
CREATE INDEX page_comments_pagetype ON page_comments(pagetype);
CREATE INDEX comment_page_ids ON page_comments(pageid);
CREATE INDEX comment_deleted ON page_comments(deleted);
CREATE INDEX comment_published ON page_comments(published);
CREATE INDEX comment_sortorder ON page_comments(sortorder);


-- ## Missing Indexes ##
CREATE INDEX page_ratings_history_user_pagetype ON page_ratings_history_user(pagetype);
CREATE INDEX page_ratings_history_anon_pagetype ON page_ratings_history_anon(pagetype);
CREATE INDEX searchindex_pageid ON searchindex(pageid);
CREATE INDEX pagemap_version ON pagemap(version);
CREATE INDEX pagemap_pagetype ON pagemap(pagetype);
CREATE INDEX pagemap_published ON pagemap(published);
CREATE INDEX pagemap_expires ON pagemap(expires);
CREATE INDEX pagemap_wf_state ON pagemap(wf_state);
CREATE INDEX objectmap_wf_state ON objectmap(wf_state);
CREATE INDEX objectmap_published ON objectmap(published);
CREATE INDEX objectmap_objectid ON objectmap(objectid);
CREATE INDEX datablock_global ON datablocks(global);
CREATE INDEX datablock_inherit ON datablocks(inherit);
CREATE INDEX properties_inherit ON properties(inherit);
CREATE INDEX properties_name ON properties(name);

-- ## More Comment Hacks ##
ALTER TABLE pagemap ADD comment_count INTEGER NOT NULL DEFAULT 0;

-------
--
-- This trigger is responsible for keeping commentcounts intact
--
CREATE OR REPLACE FUNCTION recount_comments() RETURNS TRIGGER AS '
DECLARE
    myComments          integer;
BEGIN
	SELECT count(*) FROM page_comments WHERE deleted = ''f'' AND published = ''t'' AND pageid = NEW.pageid AND pagetype = NEW.pagetype INTO myComments;
	UPDATE pagemap SET comment_count = myComments WHERE pageid = NEW.pageid AND pagetype = NEW.pagetype;
	RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER maintain_commentcount_update
AFTER UPDATE ON page_comments
FOR EACH ROW EXECUTE PROCEDURE recount_comments();

CREATE TRIGGER maintain_commentcount_insert
AFTER INSERT ON page_comments
FOR EACH ROW EXECUTE PROCEDURE recount_comments();

CREATE TRIGGER maintain_commentcount_delete
AFTER DELETE ON page_comments
FOR EACH ROW EXECUTE PROCEDURE recount_comments();


----
--  Tags
--
CREATE TABLE tags(
    id      SERIAL,
    name    VARCHAR(64),
    url     VARCHAR(64) UNIQUE,
    created TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY(id)
);

CREATE TABLE tag_page_map(
    tag_id  INTEGER NOT NULL,
    page_id INTEGER NOT NULL,
    intime  TIMESTAMP DEFAULT NOW(),
    
    PRIMARY KEY(tag_id, page_id),
    FOREIGN KEY(tag_id)   REFERENCES tags ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(page_id)  REFERENCES pagemap ON DELETE CASCADE ON UPDATE CASCADE
);







CREATE TABLE page_karma(
    pageid      INTEGER NOT NULL,
    karma       INTEGER NOT NULL DEFAULT 0,
    votes       INTEGER NOT NULL DEFAULT 0,
    
    PRIMARY KEY(pageid),
    FOREIGN KEY(pageid) REFERENCES pagemap ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE page_karma_history_anon(
    pageid      INTEGER NOT NULL,
    ip          INTEGER NOT NULL,
    karma       INTEGER NOT NULL,
    
    PRIMARY KEY(pageid, ip),
    FOREIGN KEY(pageid) REFERENCES pagemap ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE page_karma_history_user(
    pageid      INTEGER NOT NULL,
    uid         INTEGER NOT NULL,
    
    PRIMARY KEY(pageid, uid),
    FOREIGN KEY(uid)        REFERENCES user_access      ON DELETE SET NULL ON UPDATE CASCADE
);



--
-- 14.09.2010 --
--
ALTER TABLE pagemap ADD order_pos INTEGER DEFAULT 0;
ALTER TABLE structure ADD order_pos INTEGER DEFAULT 0;



-- 
-- Eh... 
--
CREATE TABLE added_files(
id SERIAL,
pageid INTEGER,
nodeid INTEGER,
filepath VARCHAR(100),
filename VARCHAR(100),
filesize VARCHAR(100),
filetype VARCHAR(100),
created VARCHAR(100),
PRIMARY KEY (id)
);
create index file_pageid on added_files(pageid);
create index file_nodeid on added_files(nodeid);